[TIPS] ELB のアクセスログのタイムスタンプを QuickSight で Date タイプとして利用する

こんにちは、藤本です。

先日、Amazon Athena のクエリ結果を SPICE にインポートして、 QuickSight で可視化するというブログをエントリしました。その時に得られた知見をもう一つご紹介します。

タイトルが何を言っているのか分かりづらいですが、、、ELB のアクセスログのタイムスタンプはフォーマット上、Athena の Timestamp にも、QuickSight の Date タイプにも適合しません。でも、グラフ表示上、Date として扱いたいです。タイムスタンプの文字列でも、Athena の場合は、timestamp like '2017-01-01T10:%'というようなクエリで、QuickSight の場合は、Start with フィルターである程度の絞り込みはできますが、柔軟なクエリをしたい場合に複雑なクエリや、細かいフィルターの指定が必要となります。何より、QuickSight による時間単位の集計ができなくなってしまいます。

そこで ELB のアクセスログのタイムスタンプを QuickSight の Date として利用する方法を考えました。

ELB のアクセスログのタイムスタンプフォーマット

まず、ELB のアクセスログのフォーマットは以下のようになります。

yyyy-MM-ddTHH:mm:ss.SSSSSSZ

例えば、日本時間の2017年1月8日18時33分40.123456秒、であれば、
2017-01-08T09:33:40.123456Z
となります。

QuickSight で Date への変換

QuickSight の Date タイプのフォーマット

QuickSight の Date タイプで認識される日付フォーマットは 35 種類あります！ELB のアクセスログのタイムスタンプの日付フォーマットは 35種類ともにヒットしません。。。

例えば、以下のようなフォーマットです。

yyyy-MM-dd, for example 2015-10-01.
yyyy-MM-ddTHH, for example 2015-10-01T16.
yyyy-MM-ddTHH:mm, for example 2015-10-01T16:08.
yyyy-MM-ddTHH:mm:ss, for example 2015-10-01T16:08:29.
yyyy-MM-ddTHH:mm:ss.SSS, for example 2015-10-01T16:08:29,322.
yyyy-MM-ddTHH:mm:ss.SSSZZ, for example 2015-10-01T16:08:29.322-08:00.

35種類は下記ドキュメントページに記載されています。

• Data Source Limits - Supported Date Formats

マイクロ秒じゃなければ、ヒットしますね。。サポートしてほしいものです。

計算フィールドを利用した変換

それでどうやって、Date タイプにするのかと言えば、計算フィールドを利用します。計算フィールドは他のフィールドの値や、固定値に関数を適用した値を新しいフィールドとして追加することができます。

計算フィールドに関しては下記エントリをご参照ください。

• データ準備機能詳解：計算フィールド #quicksight #24 | Amazon QuickSight Advent Calendar 2016

アクセスログのタイムスタンプをyyyy-MM-ddTHH:mm:ss.SSSで切り取った文字列を Date タイプに変換する計算フィールドを作成します。

それでは実際にやってみましょう。

Amazon Athena を設定する

ELB のアクセスログを Athena でクエリするには下記ブログにあるCREATE TABLEでテーブルを作成できます。

Amazon AthenaでELBログをSQLで解析する

データセットを作成する

QuickSight の New Data set の画面へ遷移します。今回は直接 Athena から取得します。Data Set に Athena を選択します。Data source name は適当に elblog とし、Create data source をクリックします。

作成したテーブルを選択し、Select をクリックします。

計算フィールドを追加するため、Edit/Preview data をクリックします。

request_timestampが文字列として認識されています。

試しに Date タイプに変えてみましょう。

エラーとなりました。

文字列に戻します。

計算フィールドから Date タイプのフィールドを作成します。New Field をクリックします。Fomula に関数を書きます。先頭 23文字（ミリ秒まで）を切り取って、その 23文字合わせた日付フォーマットで Date タイプに変換する関数です。

parseDate(left({request_timestamp}, 23), "yyyy-MM-dd'T'HH:mm:ss.SSS")

下記でも出来たけど、いいのかな。。S を 6つでマイクロ秒？

parseDate({request_timestamp}, "yyyy-MM-dd'T'HH:mm:ss.SSSSSSZ")

timestamp フィールドが表示されます。フィールドタイプも時計マークで Date タイプとして認識されています。

Save & visualize からグラフを作成します。

Date タイプですので、一時間単位のアクセス数の集計表示が可視化できました。

ELB のアクセスログの場合は、Athena のテーブル作成時の正規表現で Date タイプの日付フォーマットに合わせた方が計算フィールドを作成しなくてもいいので、楽かもしれません。

Athena で Timestamp への変換

合わせて Athena の Timestamp への変換もしらべてみました。Athena は日付タイプに Date、Timestamp をサポートしています。Date は年月日までを保存することができます。日付で利用したいことは少なそうなので今回は対象外とします。

Athena の Timestamp フォーマット

Athena が Timestamp で認識する日付フォーマットはドキュメントには言及されていません。たぶん、Presto の仕様なのかな。Presto のドキュメントには以下の記載があります。

Instant in time that includes the date and time of day without a time zone. Values of this type are parsed and rendered in the session time zone.
Example: TIMESTAMP '2001-08-22 03:04:05.321' 7.1. Data Types - TIMESTAMP

yyyy-MM-dd hh:mm:ss.SSSの日付フォーマットに対応しています。

ELB のアクセスログのタイムスタンプの日付フォーマットとは一致しませんね。

テーブルのフィールドタイプとしては Timestamp で取り込むことができなさそうですが、SELECT時にfrom_iso8601_timestamp関数を利用することで、ISO8601形式の文字列から Timestamp へ変換して取り出すことができます。

• 6.10. Date and Time Functions and Operators - from_iso8601_timestamp

以下のような感じです。

テーブル名がelb_log、ELB のタイムスタンプの文字列のカラムがrequest_timestampです。

SELECT from_iso8601_timestamp(request_timestamp) FROM elb_log

条件で利用する場合は以下のようなクエリで絞り込みを行うことができます。

SELECT count(*)
FROM (
    SELECT from_iso8601_timestamp(request_timestamp) AS time
    FROM elb_log
)
WHERE
    time>date_parse('2016-12-21', '%Y-%m-%d') 

まとめ

いかがでしたでしょうか？

QuickSight は Date タイプを扱えるかどうかで解析の幅に大きく影響します。ELB のタイプスタンプに限らず、Date タイプで認識させたいけど、認識されない値がある場合の参考になれば幸いです。